package br.fiap.nac.DAO;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.fiap.nac.Classes.Cliente;
import br.fiap.nac.Util.DBUtil;

public class ClienteDAO {
	
	public boolean inserirCliente(Cliente cliente) throws SQLException, Exception {
		
		String sql = "INSERT INTO PUBLIC.CLIENTE (nome, cpf) VALUES (?, ?)";
		
		PreparedStatement insert = DBUtil.connection.prepareStatement(sql);
		insert.setString(1, cliente.getNome());
		insert.setString(2, cliente.getCpf());
		
		insert.execute();
		int retorno = insert.getUpdateCount();

		return retorno > 0;
	}
	
	public int atualizarCliente(Cliente cliente) throws SQLException, Exception {
		
		String sql = "SELECT COUNT(id) FROM pedido where idCliente = ? ";
		
		PreparedStatement verifica = DBUtil.connection.prepareStatement(sql);
		verifica.setLong(1, cliente.getId());
		
		ResultSet rs =  verifica.executeQuery();
		
		if (rs != null) {
			rs.next();
			if (rs.getInt("C1") != 0) {

				String sql2 = "UPDATE cliente SET nome = ? where id = ?";
				PreparedStatement update = DBUtil.connection.prepareStatement(sql2);
				update.setString(1, cliente.getNome());
				update.setLong(2, cliente.getId());

				int ret = update.executeUpdate();
				if(ret == 0){
					return 3;
				}
				
				return 2;
			}
			else {
				
				String sql2 = "UPDATE cliente SET nome = ? , cpf = ? where id = ?";
				PreparedStatement update = DBUtil.connection.prepareStatement(sql2);
				update.setString(1, cliente.getNome());
				update.setString(2, cliente.getCpf());
				update.setLong(3, cliente.getId());

				update.executeUpdate();
				
				int ret = update.executeUpdate();
				
				if(ret == 0){
					return 3;
				}
				
				return 1;
			}
		}
		else
			return 0;
	}
	
	public boolean excluirCliente(Cliente cliente) throws SQLException, Exception {
		String sql = "DELETE FROM cliente WHERE id = ?";
		PreparedStatement delete = DBUtil.connection.prepareStatement(sql);
		delete.setLong(1, cliente.getId());
		
		int linha = delete.executeUpdate();
		
		if (linha > 0)
			return true;
		else 
			return false;
	}
	
	public List<Cliente> pesquisarCliente(Cliente cliente) throws SQLException, Exception {

		List<Cliente> listaClientes = new ArrayList<Cliente>();
		
		String sql = "";
		PreparedStatement select;

		if(cliente.getId() != null)
		{
			//Pesquisa por id
			sql = "SELECT ID, NOME, CPF FROM CLIENTE WHERE ID = ?";
			select = DBUtil.connection.prepareStatement(sql);
			select.setLong(1, cliente.getId());
		}
		else if(cliente.getNome() != null)
		{
			//Pesquisa pelo comeco do nome
			sql = "SELECT * FROM CLIENTE WHERE NOME LIKE '" + cliente.getNome() + "%'";
			select = DBUtil.connection.prepareStatement(sql);
		}
		else {
			//Pesquisa pelo CPF
			sql = "SELECT ID, NOME, CPF FROM CLIENTE WHERE CPF = ?";
			select = DBUtil.connection.prepareStatement(sql);
			select.setString(1, cliente.getCpf());
		}
		
		ResultSet rs = select.executeQuery();

		while (rs.next()) {
			Cliente clien = new Cliente();
			
			clien.setId(rs.getLong(1));
			clien.setNome(rs.getString(2));
			clien.setCpf(rs.getString(3));
			
			listaClientes.add(clien);
		}
		return listaClientes;
	}
	
	public Cliente pesquisarClientePorId(Long id) throws SQLException, Exception {

		Cliente cliente = null;
		
		String sql = "";
		PreparedStatement select;

		sql = "SELECT ID, NOME, CPF FROM CLIENTE WHERE ID = ?";
		select = DBUtil.connection.prepareStatement(sql);
		select.setLong(1, id);

		ResultSet rs = select.executeQuery();

		if (rs.next()) {
			cliente = new Cliente();
			cliente.setId(rs.getLong(1));
			cliente.setNome(rs.getString(2));
			cliente.setCpf(rs.getString(3));
		}

		return cliente;
	}
	
	public Cliente pesquisarClientePorCPF(String cpf) throws SQLException, Exception {

		Cliente cliente = null;
		
		String sql = "";
		PreparedStatement select;

		sql = "SELECT ID, NOME, CPF FROM CLIENTE WHERE CPF = ?";
		select = DBUtil.connection.prepareStatement(sql);
		select.setString(1, cpf);

		ResultSet rs = select.executeQuery();

		if (rs.next()) {
			cliente = new Cliente();
			cliente.setId(rs.getLong(1));
			cliente.setNome(rs.getString(2));
			cliente.setCpf(rs.getString(3));
		}

		return cliente;
	}

}
